﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Administration.Catalog
{
    public partial class recDebtorRepo : ISearchable<recDebtor>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM master.recDebtor LEFT JOIN master.recRegion ON recDebtor.RegionID = recRegion.RegionID LEFT JOIN master.recCity ON recDebtor.CityID = recCity.CityID {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM master.recDebtor LEFT JOIN master.recRegion ON recDebtor.RegionID = recRegion.RegionID LEFT JOIN master.recCity ON recDebtor.CityID = recCity.CityID {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM master.recDebtor LEFT JOIN master.recRegion ON recDebtor.RegionID = recRegion.RegionID LEFT JOIN master.recCity ON recDebtor.CityID = recCity.CityID {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.recDebtor LEFT JOIN master.recRegion ON recDebtor.RegionID = recRegion.RegionID LEFT JOIN master.recCity ON recDebtor.CityID = recCity.CityID {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM master.recDebtor LEFT JOIN master.recRegion ON recDebtor.RegionID = recRegion.RegionID LEFT JOIN master.recCity ON recDebtor.CityID = recCity.CityID {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM master.recDebtor LEFT JOIN master.recRegion ON recDebtor.RegionID = recRegion.RegionID LEFT JOIN master.recCity ON recDebtor.CityID = recCity.CityID {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
        public List<recDebtor> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recDebtor> tt = new List<recDebtor>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recDebtor.DebtorID
                                                , recDebtor.DebtorCode
                                                , recDebtor.DebtorName
                                                , recDebtor.NPWP
                                                , recDebtor.Address1
                                                , recDebtor.Address2
                                                , recDebtor.Address3
                                                , recDebtor.ZipCode
                                                , recDebtor.RegionID
                                                , recDebtor.CityID
                                                , recDebtor.Email
                                                , recDebtor.Phone
                                                , recDebtor.Fax
                                                , recDebtor.Website
                                                , recDebtor.IsActive
                                                , recDebtor.CreatedDate
                                                , recDebtor.CreatedBy
                                                , recDebtor.UpdatedDate
                                                , recDebtor.UpdatedBy
                                                , recRegion.RegionName
                                                , recCity.CityName");

                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      master.recDebtor LEFT JOIN master.recRegion ON recDebtor.RegionID = recRegion.RegionID LEFT JOIN master.recCity ON recDebtor.CityID = recCity.CityID");
                    sbQuery.AppendFormat("    {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtor t = new recDebtor(true);
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.Address1 = dr[recDebtor.Fields.Address1] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address1].ToString();
                                t.Address2 = dr[recDebtor.Fields.Address2] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address2].ToString();
                                t.Address3 = dr[recDebtor.Fields.Address3] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address3].ToString();
                                t.ZipCode = dr[recDebtor.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.ZipCode].ToString();
                                t.RegionID = dr[recDebtor.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.RegionID].ToString());
                                t.CityID = dr[recDebtor.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.CityID].ToString());
                                t.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.Phone = dr[recDebtor.Fields.Phone] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Phone].ToString();
                                t.Fax = dr[recDebtor.Fields.Fax] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Fax].ToString();
                                t.Website = dr[recDebtor.Fields.Website] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Website].ToString();
                                t.IsActive = dr[recDebtor.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtor.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtor.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtor.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtor.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.UpdatedBy].ToString();
                                t.recRegion.RegionName = dr[recRegion.Fields.RegionName] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionName].ToString();
                                t.recCity.CityName = dr[recCity.Fields.CityName] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recDebtor> GetList(string subSubSectorIds)
        {
            List<recDebtor> tt = new List<recDebtor>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @" 
                    SELECT 
                        distinct recDebtor.DebtorID , DebtorCode, DebtorName, NPWP, Address1, Address2, 
                        Address3, ZipCode, RegionID, CityID, Email, Phone, Fax, Website, master.recDebtor.IsActive,
                        recDebtor.CreatedDate, recDebtor.CreatedBy, recDebtor.UpdatedDate, 
                        recDebtor.UpdatedBy, IsIndividual 
                    FROM master.recDebtor
                    inner join master.recDebtorSector on recDebtorSector.DebtorID = recDebtor.DebtorID 
                    where recDebtor.IsActive = 1 AND recDebtorSector.SubSubSectorID IN (" + subSubSectorIds + ")";

                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtor t = new recDebtor();
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.Address1 = dr[recDebtor.Fields.Address1] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address1].ToString();
                                t.Address2 = dr[recDebtor.Fields.Address2] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address2].ToString();
                                t.Address3 = dr[recDebtor.Fields.Address3] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address3].ToString();
                                t.ZipCode = dr[recDebtor.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.ZipCode].ToString();
                                t.RegionID = dr[recDebtor.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.RegionID].ToString());
                                t.CityID = dr[recDebtor.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.CityID].ToString());
                                t.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.Phone = dr[recDebtor.Fields.Phone] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Phone].ToString();
                                t.Fax = dr[recDebtor.Fields.Fax] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Fax].ToString();
                                t.Website = dr[recDebtor.Fields.Website] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Website].ToString();
                                t.IsActive = dr[recDebtor.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtor.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtor.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtor.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtor.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.UpdatedBy].ToString();
                                t.IsIndividual = dr[recDebtor.Fields.IsIndividual] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsIndividual].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDebtor> GetListDebtorSanction()
        {
            List<recDebtor> tt = new List<recDebtor>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @" 
                                        select Distinct
	                                        master.recDebtor.DebtorID,
	                                        master.recDebtor.DebtorName,
	                                        master.recDebtorSector.DebtorSectorID 	 
                                        from master.recDebtor
                                        inner join master.recDebtorSector on (master.recDebtor.DebtorID = master.recDebtorSector.DebtorID)
                                        inner join transactions.recSanction on (master.recDebtorSector.DebtorSectorID = transactions.recSanction.DebtorSectorID)
                                        where 
                                            (transactions.recSanction.IsApprove = 1 and 
                                            master.recDebtor.IsActive = 1 and 
                                            recSanction.SanctionID not in (select SanctionID from transactions.recObjection))
                                        order by master.recDebtor.DebtorName";

                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtor t = new recDebtor(true);
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recDebtorSector.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }


        public recDebtor GetByNPWP(string key)
        {
            recDebtor t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DebtorID, DebtorCode, DebtorName, NPWP, Address1, Address2, Address3, ZipCode, a.RegionID, a.CityID, Email, Phone, Fax, Website, IsActive, a.CreatedDate, a.CreatedBy, a.UpdatedDate, a.UpdatedBy, IsIndividual, RegionName ,CityName, RegionCode, CityCode FROM master.recDebtor a left join master.recRegion b on a.RegionId = b.RegionId left join master.reccity c on a.cityid = c.cityid WHERE NPWP = @NPWP";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtor(true);
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.Address1 = dr[recDebtor.Fields.Address1] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address1].ToString();
                                t.Address2 = dr[recDebtor.Fields.Address2] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address2].ToString();
                                t.Address3 = dr[recDebtor.Fields.Address3] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address3].ToString();
                                t.ZipCode = dr[recDebtor.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.ZipCode].ToString();
                                t.RegionID = dr[recDebtor.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.RegionID].ToString());
                                t.CityID = dr[recDebtor.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.CityID].ToString());
                                t.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.Phone = dr[recDebtor.Fields.Phone] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Phone].ToString();
                                t.Fax = dr[recDebtor.Fields.Fax] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Fax].ToString();
                                t.Website = dr[recDebtor.Fields.Website] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Website].ToString();
                                t.IsActive = dr[recDebtor.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtor.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtor.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtor.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtor.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.UpdatedBy].ToString();
                                t.IsIndividual = dr[recDebtor.Fields.IsIndividual] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsIndividual].ToString());
                                t.recRegion.RegionName = dr[recRegion.Fields.RegionName] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionName].ToString();
                                t.recCity.CityName = dr[recCity.Fields.CityName] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityName].ToString();
                                t.recRegion.RegionCode = dr[recRegion.Fields.RegionCode] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionCode].ToString();
                                t.recCity.CityCode = dr[recCity.Fields.CityCode] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityCode].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public recDebtor GetByNPWPUnion(string key)
        {
            recDebtor t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select gabungan.NPWP as NPWP from( {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select NPWP from master.recDebtor mrd where NPWP=@NPWP {0}", Environment.NewLine);
                    sbQuery.AppendFormat("union {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select NPWP from transactions.recRegistration trr where NPWP=@NPWP) gabungan {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtor();
                                t.NPWP = dr["NPWP"] == DBNull.Value ? string.Empty : dr["NPWP"].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public recDebtor CheckUser(string npwp, int sectorId, int subSectorId, int subSubSectorId, string userCode)
        {
            recDebtor t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select mrd.NPWP,mru.UserID from master.recDebtor mrd {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recDebtorSector mrds on mrds.DebtorID=mrd.DebtorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUserSector mrus on mrus.DebtorSectorID=mrds.DebtorSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUser mru on mru.UserID=mrus.UserID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where mrd.NPWP=@NPWP and mrds.SectorID=@SectorId and mrds.SubSectorID=@SubSectorId and mrds.SubSubSectorID=@SubSubSectorId and mru.UserCode=@UserCode {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = npwp;
                    cmd.Parameters.Add("@SectorId", SqlDbType.Int).Value = sectorId;
                    cmd.Parameters.Add("@SubSectorId", SqlDbType.Int).Value = subSectorId;
                    cmd.Parameters.Add("@SubSubSectorId", SqlDbType.Int).Value = subSubSectorId;
                    cmd.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = userCode;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtor(true);
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.recUser.UserID = dr[recUser.Fields.UserID] == DBNull.Value ? 0 : Convert.ToInt32(dr[recUser.Fields.UserID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public recDebtor CheckUser(string npwp, string userCode)
        {
            recDebtor t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select mrd.NPWP,mru.UserID from master.recDebtor mrd {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recDebtorSector mrds on mrds.DebtorID=mrd.DebtorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUserSector mrus on mrus.DebtorSectorID=mrds.DebtorSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUser mru on mru.UserID=mrus.UserID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where mrd.NPWP=@NPWP and (lower(mru.UserCode)=lower(@UserCode)) {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = npwp;
                    cmd.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = userCode;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtor(true);
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.recUser.UserID = dr[recUser.Fields.UserID] == DBNull.Value ? 0 : Convert.ToInt32(dr[recUser.Fields.UserID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }



        public recDebtor CheckUserUnion(string npwp, string userCode)
        {
            recDebtor t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("select gabungan.NPWP as NPWP,gabungan.UserID as UserID from ( {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select mrd.NPWP,mru.UserID from master.recDebtor mrd {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recDebtorSector mrds on mrds.DebtorID=mrd.DebtorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUserSector mrus on mrus.DebtorSectorID=mrds.DebtorSectorID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUser mru on mru.UserID=mrus.UserID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where mrd.NPWP=@NPWP and (lower(mru.UserCode)=lower(@UserCode)) {0}", Environment.NewLine);
                    sbQuery.AppendFormat("union {0}", Environment.NewLine);
                    sbQuery.AppendFormat("select trr.NPWP,mru.UserID from transactions.recRegistration trr {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join transactions.recSectorRegistration trsr on trsr.RegistrationID=trr.RegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUserSector mrus on mrus.RegisterSectorID=trsr.SectorRegistrationID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recUser mru on mru.UserID=mrus.UserID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("where trr.NPWP=@NPWP and (lower(mru.UserCode)=lower(@UserCode))) gabungan {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = npwp;
                    cmd.Parameters.Add("@UserCode", SqlDbType.VarChar).Value = userCode;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtor(true);
                                t.NPWP = dr["NPWP"] == DBNull.Value ? string.Empty : dr["NPWP"].ToString();
                                t.recUser.UserID = dr["UserID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["UserID"].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
        public int GetMaxID(int perorangan)
        {
            int result = 1;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT MAX(CONVERT(INT,SUBSTRING(DebtorCode,2,7))) DebtorCode FROM master.recDebtor where SUBSTRING(DebtorCode,1,1) = @Perorangan)
	                                    set @max = (SELECT MAX(CONVERT(INT,SUBSTRING(DebtorCode,2,7)))+1 DebtorCode FROM master.recDebtor where SUBSTRING(DebtorCode,1,1) = @Perorangan);
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@Perorangan", SqlDbType.VarChar).Value = perorangan;
                    try
                    {
                        conn.Open();
                        var objId = cmd.ExecuteScalar();
                        result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }
        public int generatecode(bool IsPerson)
        {
            int result = 0;
            string kondisi = string.Empty;
            SqlConnection conn = new SqlConnection(connectionString);
            if (IsPerson == true)
            {
                try
                {
                    SqlCommand GetCode = new SqlCommand("select max(convert(int,debtorcode)) from master.recDebtor where convert(int,DebtorCode)<20000000", conn);

                    conn.Open();
                    result = Convert.ToInt32(GetCode.ExecuteScalar()) + 1;
                    if (result < 10000000)
                    {
                        result = 10000001;
                    }
                }
                catch { result = 10000001; }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            else
            {
                try
                {
                    SqlCommand GetCode = new SqlCommand("select max(convert(int,debtorcode)) from master.recDebtor where convert(int,DebtorCode)>20000000", conn);

                    conn.Open();
                    result = Convert.ToInt32(GetCode.ExecuteScalar()) + 1;
                }
                catch { result = 20000001; }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public List<recDebtor> GetListByCorporateAction(string sectorCode, string subSubSectorCodes)
        {
            List<recDebtor> tt = new List<recDebtor>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        select 
		                                        Distinct 
		                                        recDebtor.[DebtorID]
		                                        ,recDebtor.[DebtorCode]
		                                        ,recDebtor.[DebtorName]
		                                        ,recDebtor.[NPWP]
		                                        ,recDebtor.[Address1]
		                                        ,recDebtor.[Address2]
		                                        ,recDebtor.[Address3]
		                                        ,recDebtor.[ZipCode]
		                                        ,recDebtor.[RegionID]
		                                        ,recDebtor.[CityID]
		                                        ,recDebtor.[Email]
		                                        ,recDebtor.[Phone]
		                                        ,recDebtor.[Fax]
		                                        ,recDebtor.[Website]
		                                        ,recDebtor.[IsActive]
		                                        ,recDebtor.[CreatedDate]
		                                        ,recDebtor.[CreatedBy]
		                                        ,recDebtor.[UpdatedDate]
		                                        ,recDebtor.[UpdatedBy]
		                                        ,recDebtor.[IsIndividual]
                                        from 
                                        master.recDebtor
                                        inner join master.recDebtorSector on (recDebtor.DebtorID = recDebtorSector.DebtorID)
                                        inner join master.recSector on (recDebtorSector.SectorID = recSector.SectorID AND recSector.SectorCode = '" + sectorCode + @"')
                                        inner join master.recSubSector on (recDebtorSector.SubSectorID = recSubSector.SubSectorID AND recSubSector.SubSectorCode IN(" + subSubSectorCodes + @")) 
                                        WHERE recDebtor.IsActive = 1 ";

                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtor t = new recDebtor();
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.Address1 = dr[recDebtor.Fields.Address1] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address1].ToString();
                                t.Address2 = dr[recDebtor.Fields.Address2] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address2].ToString();
                                t.Address3 = dr[recDebtor.Fields.Address3] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address3].ToString();
                                t.ZipCode = dr[recDebtor.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.ZipCode].ToString();
                                t.RegionID = dr[recDebtor.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.RegionID].ToString());
                                t.CityID = dr[recDebtor.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.CityID].ToString());
                                t.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.Phone = dr[recDebtor.Fields.Phone] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Phone].ToString();
                                t.Fax = dr[recDebtor.Fields.Fax] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Fax].ToString();
                                t.Website = dr[recDebtor.Fields.Website] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Website].ToString();
                                t.IsActive = dr[recDebtor.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtor.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtor.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtor.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtor.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.UpdatedBy].ToString();
                                t.IsIndividual = dr[recDebtor.Fields.IsIndividual] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsIndividual].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool SetActivation(recDebtor domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE master.recDebtor SET IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE DebtorID = @DebtorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = domain.DebtorID;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = domain.IsActive;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public List<recDebtor> GetListById(int key)
        {
            List<recDebtor> tt = new List<recDebtor>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DebtorID, DebtorCode, DebtorName, NPWP, Address1, Address2, Address3, ZipCode, RegionID, CityID, Email, Phone, Fax, Website, IsActive, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, IsIndividual FROM master.recDebtor WHERE DebtorID = @DebtorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtor t = new recDebtor();
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.Address1 = dr[recDebtor.Fields.Address1] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address1].ToString();
                                t.Address2 = dr[recDebtor.Fields.Address2] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address2].ToString();
                                t.Address3 = dr[recDebtor.Fields.Address3] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address3].ToString();
                                t.ZipCode = dr[recDebtor.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.ZipCode].ToString();
                                t.RegionID = dr[recDebtor.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.RegionID].ToString());
                                t.CityID = dr[recDebtor.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.CityID].ToString());
                                t.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.Phone = dr[recDebtor.Fields.Phone] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Phone].ToString();
                                t.Fax = dr[recDebtor.Fields.Fax] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Fax].ToString();
                                t.Website = dr[recDebtor.Fields.Website] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Website].ToString();
                                t.IsActive = dr[recDebtor.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtor.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtor.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtor.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtor.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.UpdatedBy].ToString();
                                t.IsIndividual = dr[recDebtor.Fields.IsIndividual] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsIndividual].ToString());

                                tt.Add(t);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public recDebtor GetByDebtorCode(string key)
        {
            recDebtor t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DebtorID, DebtorCode, DebtorName, NPWP, Address1, Address2, Address3, ZipCode, a.RegionID, a.CityID, Email, Phone, Fax, Website, IsActive, a.CreatedDate, a.CreatedBy, a.UpdatedDate, a.UpdatedBy, IsIndividual, RegionName ,CityName, RegionCode, CityCode FROM master.recDebtor a left join master.recRegion b on a.RegionId = b.RegionId left join master.reccity c on a.cityid = c.cityid WHERE DebtorCode = @DebtorCode";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorCode", SqlDbType.VarChar).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDebtor(true);
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.Address1 = dr[recDebtor.Fields.Address1] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address1].ToString();
                                t.Address2 = dr[recDebtor.Fields.Address2] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address2].ToString();
                                t.Address3 = dr[recDebtor.Fields.Address3] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address3].ToString();
                                t.ZipCode = dr[recDebtor.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.ZipCode].ToString();
                                t.RegionID = dr[recDebtor.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.RegionID].ToString());
                                t.CityID = dr[recDebtor.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.CityID].ToString());
                                t.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.Phone = dr[recDebtor.Fields.Phone] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Phone].ToString();
                                t.Fax = dr[recDebtor.Fields.Fax] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Fax].ToString();
                                t.Website = dr[recDebtor.Fields.Website] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Website].ToString();
                                t.IsActive = dr[recDebtor.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtor.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtor.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtor.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtor.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.UpdatedBy].ToString();
                                t.IsIndividual = dr[recDebtor.Fields.IsIndividual] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsIndividual].ToString());
                                t.recRegion.RegionName = dr[recRegion.Fields.RegionName] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionName].ToString();
                                t.recCity.CityName = dr[recCity.Fields.CityName] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityName].ToString();
                                t.recRegion.RegionCode = dr[recRegion.Fields.RegionCode] == DBNull.Value ? string.Empty : dr[recRegion.Fields.RegionCode].ToString();
                                t.recCity.CityCode = dr[recCity.Fields.CityCode] == DBNull.Value ? string.Empty : dr[recCity.Fields.CityCode].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

    }

}
